package cn.hchaojie.web.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class DemoCrud {
	Connection connection = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	
	@Before
	public void init() {
		// 加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		// 连接数据库
		try {
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila?rewriteBatchedStatements=true", "root", "root");

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testAdd() {
		try {
			// 发送sql语句
ps = connection.prepareStatement("insert into actor (first_name, last_name) values ('宝强', '王')");
int result = ps.executeUpdate();
System.out.println("result:" + result);
			
			// 处理结果
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@After
	public void clear() {
		// 关闭资源
		closeQuietly(rs);
		closeQuietly(ps);
		closeQuietly(connection);
	}
	
	@Test
	public void testUpdate() {
		try {
			ps = connection.prepareStatement("update actor set first_name = 'hchaojie'");
			int result = ps.executeUpdate();
			System.out.println("result:" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testDelete() {
		try {
			ps = connection.prepareStatement("delete from actor where actor_id in (211, 212)");
			int result = ps.executeUpdate();
			System.out.println("result:" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testQuery() {
		try {
			ps = connection.prepareStatement("select film_id, last_update, title, description from film limit 10");
			ResultSet rs = ps.executeQuery();
			
			while (rs.next()) {	// 每次while循环处理一条数据
				// 遍历每一列
				String format = "file_id:%d last_update:%s title:%s description:%s";
				String msg = String.format(format, rs.getInt(1), rs.getDate(2), rs.getString(3), rs.getString(4));
				System.out.println(msg);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testNormalInsert() {
		long start = System.currentTimeMillis();
		try {
			for (int i = 0; i < 1000; i++) {
				PreparedStatement ps = connection.prepareStatement("insert into actor(first_name, last_name) values (?, ?)");
				ps.setString(1, "宝强" + i);
				ps.setString(2, "王" + i);
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		long end = System.currentTimeMillis();
		System.out.println("耗时：" + (end - start));
	}
	
	@Test
	public void testBatchInsert() {
		long start = System.currentTimeMillis();
		try {
			PreparedStatement ps = connection.prepareStatement("insert into actor(first_name, last_name) values (?, ?)");
			for (int i = 0; i < 10000; i++) {
				ps.setString(1, "宝强" + i);
				ps.setString(2, "王" + i);
				ps.addBatch();
				
				if (i % 1000 == 0) {
					ps.executeBatch();
					ps.clearBatch();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		long end = System.currentTimeMillis();
		System.out.println("耗时：" + (end - start));
	}
	
	@Test
	public void testSqlInject() {
		try {
			// sql不要拼字符串
			String name = "Mike' or '1=1";
			String password = "xxx";
//			ps = connection.prepareStatement("select * from staff where last_name ='"
//					+ name + "' and password = '" + password + "'");
			// 参数化查询，对特殊字符进行转义，避免sql注入的问题
			ps = connection.prepareStatement("select * from staff where last_name =? and password=?");
			ps.setString(1, name);
			ps.setString(2, password);
			
			System.out.println(ps);
			
			ResultSet rs = ps.executeQuery();
			
			while (rs.next()) {	// 每次while循环处理一条数据
				// 遍历每一列
				String format = "file_id:%d last_update:%s title:%s description:%s";
				String msg = String.format(format, rs.getInt(1), rs.getDate(2), rs.getString(3), rs.getString(4));
				System.out.println(msg);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static void closeQuietly(AutoCloseable closeable) {
		if (closeable != null) {
			try {
				closeable.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}
